package com.mison.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;



import com.mison.model.MisonFeedback;
import com.mison.model.MisonFeedbackPerson;
import com.mison.model.MisonStudent;

import com.mison.utils.DBUtils;

import com.mison.utils.Page;

public class GuostDao {

	private final Logger logger = Logger.getLogger(GuostDao.class);
	
	public List<MisonFeedback> getList(Page<MisonFeedback> page,String content) {
		
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<MisonFeedback> list = new ArrayList<MisonFeedback>();
		
		
		String sql = "select * from mison_feedback where content like ? limit ?,?";
		
		try {
			ps = conn.prepareStatement(sql);			
			ps.setString(1, "%"+content+"%");
			ps.setInt(2, (page.getCurrPage()-1)*page.getPerPage());
			ps.setInt(3, page.getPerPage());
			
			logger.info(ps);
			
			rs = ps.executeQuery();
			while(rs.next()){
				MisonFeedback mf = new MisonFeedback();
				MisonStudent ms = new MisonStudent();
				MisonFeedbackPerson mp = new MisonFeedbackPerson();
				mf.setMisonStudent(ms);
				mf.setMisonFeedbackPerson(mp);
				mf.setFeedbackId(rs.getInt("feedback_id"));
				ms.setStudentId(rs.getInt("student_id"));
				mp.setFeedbackPersonId(rs.getInt("feedback_person_id"));
				mf.setContent(rs.getString("content"));
				list.add(mf);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}
	public int getCount(String content) {
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "select count(*) from mison_feedback where content like ?";
		
		try {
			ps = conn.prepareStatement(sql);
			
			if(content == null){
				content = "";
			}
			
			ps.setString(1, "%"+content+"%");
			
			logger.info(ps);
			
			rs = ps.executeQuery();
			
			rs.next();
			
			return rs.getInt("count(*)");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return 0;
	}
	public List<MisonFeedback> getSelectList(Page<MisonFeedback> page,
			int to_id) {
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<MisonFeedback> list = new ArrayList<MisonFeedback>();
		
		
		String sql = "select * from mison_feedback where feedback_id=?";
		
		try {
			ps = conn.prepareStatement(sql);			
			ps.setInt(1, to_id);
			logger.info(ps);
			
			rs = ps.executeQuery();
			while(rs.next()){
				MisonFeedback mf = new MisonFeedback();
				MisonStudent ms = new MisonStudent();
				MisonFeedbackPerson mp = new MisonFeedbackPerson();
				mf.setMisonStudent(ms);
				mf.setMisonFeedbackPerson(mp);
				mf.setFeedbackId(rs.getInt("feedback_id"));
				ms.setStudentId(rs.getInt("student_id"));
				mp.setFeedbackPersonId(rs.getInt("feedback_person_id"));
				mf.setContent(rs.getString("content"));
				list.add(mf);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}
	public void updateSelectList(MisonFeedback mf,
			int toId) {
		
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		
		
		String sql = "UPDATE mison_feedback SET feedback_id = ? , student_id = ? , feedback_person_id =? , content=? WHERE feedback_id = ?";
		
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, mf.getFeedbackId());
			ps.setInt(2, mf.getMisonStudent().getStudentId());
			ps.setInt(3, mf.getMisonFeedbackPerson().getFeedbackPersonId());
			ps.setString(4, mf.getContent());
			ps.setInt(5, toId);
			
			
			logger.info(ps);
			
			ps.executeUpdate();
			
			
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}
	public void getDeleteList(int toId) {
		
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		
		
		String sql = "delete from mison_feedback where feedback_id = ?";
		
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, toId);
			
			
			logger.info(ps);
			
			ps.executeUpdate();
			
			
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}
